using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;


namespace Myproject.DataAccess.factory
{
    public class SqlAdoService
    {
        //private log4net.ILog logger = log4net.LogManager.GetLogger("MyLogger"); 
        private static SqlAdoService theInstance = null;
        private static readonly object padlock = new object();
        public static SqlAdoService Instance
        {

            get
            {
                if (SqlAdoService.theInstance == null)
                {
                    lock (padlock)
                    {
                        if (SqlAdoService.theInstance == null)
                        {

                            SqlAdoService.theInstance = new SqlAdoService(ConfigurationManager.ConnectionStrings["MasterConnectionString"].ConnectionString);
                        }
                    }
                }
                return SqlAdoService.theInstance;
            }
        }


        private SqlAdoService()
        {
        }
        public SqlAdoService(string ConnectionString)
        {
            this.connectionString = ConnectionString;
        }

        private string connectionString;
        public string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        public SqlDataReader GetDataReaderBySql(string sql)
        {
            return GetDataReaderBySqlWithParameters(sql, null, null);
        }
        public SqlDataReader GetDataReaderBySql(string sql, SqlTransaction sqlTransaction)
        {
            return GetDataReaderBySqlWithParameters(sql, null, sqlTransaction);
        }
        public SqlDataReader GetDataReaderBySqlWithParameters(string sql, IList paramList)
        {
            return GetDataReaderBySqlWithParameters(sql, paramList, null);
        }
        public SqlDataReader GetDataReaderBySqlWithParameters(string sql, IList paramList, SqlTransaction sqlTransaction)
        {

            //logger.Debug("DB Access Start Time:"+DateTime.Now.ToString());

            string strSql = sql;
            SqlConnection sqlCon = null;
            SqlDataReader dbDataReader = null;
            SqlCommand sqlCmd = new SqlCommand();

            try
            {

                if (sqlTransaction != null)
                {
                    sqlCon = sqlTransaction.Connection;
                    sqlCmd.Transaction = sqlTransaction;
                }
                else
                {
                    sqlCon = new SqlConnection(connectionString);

                }

                sqlCmd.Connection = sqlCon;
                sqlCmd.CommandText = sql;
                sqlCmd.CommandTimeout = sqlCon.ConnectionTimeout;

                if (paramList != null)
                {
                    for (int i = 0; i < paramList.Count; i++)
                    {
                        if ((paramList[i] != null) &&
                            (paramList[i].ToString().Trim() != String.Empty))
                        {
                            sqlCmd.Parameters.AddWithValue("@" + i.ToString(), paramList[i]);
                        }
                    }
                }

                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }
                if (sqlTransaction == null)
                {
                    dbDataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    dbDataReader = sqlCmd.ExecuteReader();
                }

            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                sqlCon.Close();
                sqlCmd.Dispose();
                sqlCon.Dispose();
            }
            //logger.Debug("DB Access End Time:"+DateTime.Now.ToString());
            return dbDataReader;
        }

        public DataSet GetDataSetBySql(string sql)
        {
            return GetDataSetBySqlWithParameters(sql, null, null);
        }
        public DataSet GetDataSetBySql(string sql, SqlTransaction sqlTransaction)
        {
            return GetDataSetBySqlWithParameters(sql, null, sqlTransaction);
        }
        public DataSet GetDataSetBySqlWithParameters(string sql, IList paramList)
        {
            return GetDataSetBySqlWithParameters(sql, paramList, null);
        }

        public void ExecuteSqlBulkCopy(DataTable dt, String TableName)
        {
            try
            {
                //logger.Debug("DB Access Start Time:" + DateTime.Now.ToString());
                SqlConnection conn = new SqlConnection(connectionString);
                SqlBulkCopy bulk = new SqlBulkCopy(conn);
                bulk.DestinationTableName = TableName;
                bulk.BatchSize = dt.Rows.Count;
                bulk.BulkCopyTimeout = 180;


                if (dt != null && dt.Rows.Count != 0)
                {
                    conn.Open();
                    bulk.WriteToServer(dt);
                }
                bulk.Close();
                conn.Close();
                //logger.Debug("DB Access End Time:" + DateTime.Now.ToString());
            }
            catch (Exception e)
            {
                throw e;
            }

        }

        public void CheckConnectionStatus()
        {
            try
            {
                //logger.Debug("DB Access Start Time:" + DateTime.Now.ToString());
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
                conn.Close();
                conn.Dispose();
                //logger.Debug("DB Access End Time:" + DateTime.Now.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataSet GetDataSetBySqlWithParameters(string sql, IList paramList, SqlTransaction sqlTransaction)
        {
            //logger.Debug("DB Access Start Time:" + DateTime.Now.ToString());
            string strSql = sql;
            DataSet ds = new DataSet();
            SqlConnection sqlCon = null;
            SqlCommand sqlCmd = new SqlCommand();

            try
            {

                if (sqlTransaction != null)
                {
                    sqlCon = sqlTransaction.Connection;
                    sqlCmd.Transaction = sqlTransaction;
                }
                else
                {
                    sqlCon = new SqlConnection(connectionString);
                }

                sqlCmd.Connection = sqlCon;
                sqlCmd.CommandText = sql;
                sqlCmd.CommandTimeout = sqlCon.ConnectionTimeout;

                SqlDataAdapter sqlAd = new SqlDataAdapter();
                sqlAd.SelectCommand = sqlCmd;
                if (paramList != null)
                {
                    for (int i = 0; i < paramList.Count; i++)
                    {
                        SqlParameterAdoNet param = (SqlParameterAdoNet)paramList[i];
                        SqlParameter sqlParameter = GetParameterInput(param);

                        if (sqlParameter.ParameterName == null)
                        {
                            sqlParameter.ParameterName = "@" + i.ToString();
                        }

                        sqlCmd.Parameters.Add(sqlParameter);
                    }
                }
                sqlAd.Fill(ds, "sql");
                sqlCon.Close();
                sqlCmd.Dispose();
                sqlCon.Dispose();
            }
            catch (Exception er)
            {
                throw er;
            }
            //logger.Debug("DB Access End Time:" + DateTime.Now.ToString());
            return ds;
        }

        public int ExeSql(string strSql)
        {
            IList<string> sqlList = new List<string>();
            sqlList.Add(strSql);
            return ExeSql(sqlList, null);
        }
        public int ExeSql(string strSql, SqlTransaction sqlTransaction)
        {
            IList<string> sqlList = new List<string>();
            sqlList.Add(strSql);
            return ExeSql(sqlList, sqlTransaction);
        }

        public int ExeSql(IList<string> sqlList)
        {
            return ExeSqlListWithParameters(sqlList, null, null);
        }
        public int ExeSql(IList<string> sqlList, SqlTransaction sqlTransaction)
        {
            return ExeSqlListWithParameters(sqlList, null, sqlTransaction);
        }

        public int ExeSqlWithParameters(string strSql, IList paramList)
        {
            return ExeSqlWithParameters(strSql, paramList, null);
        }


        public int ExeSqlWithParameters(string strSql, IList paramList, SqlTransaction sqlTransaction)
        {
            IList<string> sqlList = new List<string>();
            sqlList.Add(strSql);

            IList<IList> paramListList = new List<IList>();
            paramListList.Add(paramList);

            return ExeSqlListWithParameters(sqlList, paramListList, sqlTransaction);
        }

        public int ExeSqlListWithParameters(IList<string> strSqlList, IList<IList> paramListList)
        {
            return ExeSqlListWithParameters(strSqlList, paramListList, null);
        }
        public int ExeSqlListWithParameters(IList<string> strSqlList, IList<IList> paramListList, SqlTransaction sqlTransaction)
        {
            return ExeSqlListWithParametersType(strSqlList, paramListList, sqlTransaction);
        }

        public int ExeSqlListWithParametersType(IList<string> strSqlList, IList<IList> paramListList, SqlTransaction sqlTransaction)
        {
            //logger.Debug("DB Access Start Time:" + DateTime.Now.ToString());
            int result = 0;
            SqlConnection sqlCon = null;

            try
            {
                if (strSqlList != null)
                {
                    SqlCommand sqlCmd = new SqlCommand();

                    if (sqlTransaction != null)
                    {
                        sqlCon = sqlTransaction.Connection;
                        sqlCmd.Transaction = sqlTransaction;
                    }
                    else
                    {
                        sqlCon = new SqlConnection(connectionString);
                    }

                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    sqlCmd.Connection = sqlCon;
                    sqlCmd.CommandTimeout = sqlCon.ConnectionTimeout;

                    for (int i = 0; i < strSqlList.Count; i++)
                    {
                        sqlCmd.CommandText = strSqlList[i].ToString();
                        sqlCmd.Parameters.Clear();

                        if ((paramListList != null) && (paramListList[i] != null))
                        {
                            IList list = paramListList[i];
                            for (int l = 0; l < list.Count; l++)
                            {
                                SqlParameterAdoNet param = (SqlParameterAdoNet)list[l];
                                SqlParameter sqlParameter = GetParameterInput(param);

                                if (sqlParameter.ParameterName == null)
                                {
                                    sqlParameter.ParameterName = "@" + l.ToString();
                                }

                                sqlCmd.Parameters.Add(sqlParameter);
                            }
                        }
                        result = result + sqlCmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                if (sqlTransaction == null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
            //logger.Debug("DB Access End Time:" + DateTime.Now.ToString());
            return result;
        }



        public int ExePro(string proName)
        {
            IList<string> sqlList = new List<string>();
            sqlList.Add(proName);
            return ExePro(sqlList, null);
        }
        public int ExePro(string proName, SqlTransaction sqlTransaction)
        {
            IList<string> sqlList = new List<string>();
            sqlList.Add(proName);
            return ExePro(sqlList, sqlTransaction);
        }

        public int ExePro(IList<string> proNameList)
        {
            return ExeProListWithParameters(proNameList, null, null);
        }
        public int ExePro(IList<string> proNameList, SqlTransaction sqlTransaction)
        {
            return ExeProListWithParameters(proNameList, null, sqlTransaction);
        }

        public int ExeProWithParameters(string proName, IList paramList)
        {
            return ExeProWithParameters(proName, paramList, null);
        }

        public int ExeProWithParameters(string proName, IList paramList, SqlTransaction sqlTransaction)
        {
            IList<string> sqlList = new List<string>();
            sqlList.Add(proName);

            IList<IList> paramListList = new List<IList>();
            paramListList.Add(paramList);

            return ExeProListWithParametersType(sqlList, paramListList, sqlTransaction);
        }

        public int ExeProListWithParameters(IList<string> strSqlList, IList<IList> paramListList)
        {
            return ExeProListWithParametersType(strSqlList, paramListList, null);
        }
        public int ExeProListWithParameters(IList<string> strSqlList, IList<IList> paramListList, SqlTransaction sqlTransaction)
        {
            return ExeProListWithParametersType(strSqlList, paramListList, sqlTransaction);
        }


        public int ExeProListWithParametersType(IList<string> strSqlList, IList<IList> paramListList, SqlTransaction sqlTransaction)
        {
            //logger.Debug("DB Access Start Time:" + DateTime.Now.ToString());
            int result = 0;
            SqlConnection sqlCon = null;

            try
            {
                if (strSqlList != null)
                {
                    SqlCommand sqlCmd = new SqlCommand();

                    sqlCmd.CommandType = CommandType.StoredProcedure;

                    if (sqlTransaction != null)
                    {
                        sqlCon = sqlTransaction.Connection;
                        sqlCmd.Transaction = sqlTransaction;
                    }
                    else
                    {
                        sqlCon = new SqlConnection(connectionString);
                    }

                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    sqlCmd.Connection = sqlCon;
                    sqlCmd.CommandTimeout = sqlCon.ConnectionTimeout;

                    for (int i = 0; i < strSqlList.Count; i++)
                    {
                        sqlCmd.CommandText = strSqlList[i].ToString();
                        sqlCmd.Parameters.Clear();

                        if ((paramListList != null) && (paramListList[i] != null))
                        {
                            IList list = paramListList[i];
                            for (int l = 0; l < list.Count; l++)
                            {

                                StoredProcedureParameterAdoNet param = (StoredProcedureParameterAdoNet)list[l];
                                SqlParameter sqlParameter = GetParameterInput(param);

                                if (sqlParameter.ParameterName == null)
                                {
                                    sqlParameter.ParameterName = "@" + l.ToString();
                                }
                                sqlCmd.Parameters.Add(sqlParameter);
                            }
                        }
                        result = result + sqlCmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                if (sqlTransaction == null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
            //logger.Debug("DB Access End Time:" + DateTime.Now.ToString());
            return result;
        }

        public DataSet GetDataSetByPro(string proName, IList paramList)
        {
            return GetDataSetByPro(proName, paramList, null);
        }
        public DataSet GetDataSetByPro(string proName, IList paramList, SqlTransaction sqlTransaction)
        {
            IList<string> proNameList = new List<string>();
            proNameList.Add(proName);

            IList<IList> paramListList = new List<IList>();
            paramListList.Add(paramList);

            return GetDataSetByPro(proNameList, paramListList, sqlTransaction)[0];
        }
        public IList<DataSet> GetDataSetByPro(IList<string> proNameList, IList<IList> paramListList)
        {
            return GetDataSetByPro(proNameList, paramListList);
        }
        public IList<DataSet> GetDataSetByPro(IList<string> proNameList, IList<IList> paramListList, SqlTransaction sqlTransaction)
        {
            //logger.Debug("DB Access Start Time:" + DateTime.Now.ToString());
            SqlConnection sqlCon = null;

            IList<DataSet> dsList = new List<DataSet>();
            try
            {
                if (proNameList != null)
                {
                    SqlCommand sqlCmd = new SqlCommand();

                    if (sqlTransaction != null)
                    {
                        sqlCon = sqlTransaction.Connection;
                        sqlCmd.Transaction = sqlTransaction;
                    }
                    else
                    {
                        sqlCon = new SqlConnection(connectionString);
                    }

                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    sqlCmd.Connection = sqlCon;
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandTimeout = sqlCon.ConnectionTimeout;

                    for (int i = 0; i < proNameList.Count; i++)
                    {
                        sqlCmd.CommandText = proNameList[i].ToString();
                        sqlCmd.Parameters.Clear();

                        DataSet ds = new DataSet();

                        if ((paramListList != null) && (paramListList[i] != null))
                        {
                            IList list = paramListList[i];
                            for (int l = 0; l < list.Count; l++)
                            {
                                StoredProcedureParameterAdoNet param = (StoredProcedureParameterAdoNet)list[l];
                                SqlParameter sqlParameter = GetParameterInput(param);

                                if (sqlParameter.ParameterName == null)
                                {
                                    sqlParameter.ParameterName = "@" + l.ToString();
                                }

                                sqlParameter.Direction = param.ParameterDirection;
                                sqlCmd.Parameters.Add(sqlParameter);
                            }
                        }
                        SqlParameter sqlParameterReturn = new SqlParameter();

                        SqlDataAdapter sdq = new SqlDataAdapter(sqlCmd);
                        sdq.Fill(ds);
                        dsList.Add(ds);
                    }
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                if (sqlTransaction == null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
            //logger.Debug("DB Access End Time:" + DateTime.Now.ToString());
            return dsList;
        }

        public SqlParameter GetParameterInput(object parameter)
        {
            return GetParameter(parameter, ParameterDirection.Input);
        }
        public SqlParameter GetParameter(object parameter, ParameterDirection parameterDirection)
        {
            return GetParameter(parameter, null, parameterDirection);
        }
        public SqlParameter GetParameterInput(StoredProcedureParameterAdoNet parameter)
        {
            return GetParameter(parameter.Parameter, parameter.ParameterName, parameter.ParameterDirection);
        }
        public SqlParameter GetParameterInput(SqlParameterAdoNet parameter)
        {
            return GetParameter(parameter.Parameter, parameter.ParameterName, ParameterDirection.Input);
        }

        public SqlParameter GetParameter(object parameter, string paraName, ParameterDirection parameterDirection)
        {
            Type type = null;
            if (parameter != null)
            {
                type = parameter.GetType();
            }

            SqlParameter sqlParameter = new SqlParameter();

            sqlParameter.Direction = parameterDirection;
            sqlParameter.IsNullable = true;
            sqlParameter.Value = parameter;
            sqlParameter.ParameterName = paraName;

            #region type
            if (type == null)
            {
                sqlParameter.DbType = DbType.String;
                sqlParameter.Value = DBNull.Value;
            }
            else if (type == typeof(string))
            {
                sqlParameter.DbType = DbType.String;
            }
            else if (type == typeof(DateTime))
            {
                sqlParameter.DbType = DbType.Date;
            }
            else if (type == typeof(Int32))
            {
                sqlParameter.DbType = DbType.Int32;
            }
            else if (type == typeof(double))
            {
                sqlParameter.DbType = DbType.Double;
            }
            else if (type == typeof(Decimal))
            {
                sqlParameter.DbType = DbType.Decimal;
            }
            else if (type == typeof(Int64))
            {
                sqlParameter.DbType = DbType.Int64;
            }
            else if (type == typeof(Array))
            {
                sqlParameter.DbType = DbType.Binary;
            }
            else if (type == typeof(Boolean))
            {
                sqlParameter.DbType = DbType.Boolean;
            }

            else if (type == typeof(byte[]))
            {
                sqlParameter.DbType = DbType.Binary;
            }
            else if (type == typeof(object))
            {
                sqlParameter.DbType = DbType.Object;
            }
            else if (type == typeof(Single))
            {
                sqlParameter.DbType = DbType.Single;
            }
            else if (type == typeof(Int16))
            {
                sqlParameter.DbType = DbType.Int16;
            }
            else if (type == typeof(sbyte))
            {
                sqlParameter.DbType = DbType.SByte;
            }
            else if (type == typeof(UInt64))
            {
                sqlParameter.DbType = DbType.UInt64;
            }
            else if (type == typeof(UInt32))
            {
                sqlParameter.DbType = DbType.UInt32;
            }
            else if (type == typeof(UInt16))
            {
                sqlParameter.DbType = DbType.UInt16;
            }
            else if (type == typeof(Guid))
            {
                sqlParameter.DbType = DbType.Guid;
            }

            #endregion type

            return sqlParameter;
        }
    }

    public class StoredProcedureParameterAdoNet
    {
        private object parameter;
        private ParameterDirection parameterDirection;
        private string parameterName;

        public object Parameter { get { return parameter; } set { parameter = value; } }
        public ParameterDirection ParameterDirection { get { return parameterDirection; } set { parameterDirection = value; } }
        public string ParameterName { get { return parameterName; } set { parameterName = value; } }

        public StoredProcedureParameterAdoNet(object parameter, ParameterDirection parameterDirection)
        {
            this.parameter = parameter;
            this.parameterDirection = parameterDirection;
        }
        public StoredProcedureParameterAdoNet(object parameter, string paraName, ParameterDirection parameterDirection)
        {
            this.parameter = parameter;
            this.parameterDirection = parameterDirection;
            this.parameterName = paraName;
        }

    }

    public class SqlParameterAdoNet
    {
        private object parameter;
        private string parameterName;

        public object Parameter { get { return parameter; } set { parameter = value; } }
        public string ParameterName { get { return parameterName; } set { parameterName = value; } }

        public SqlParameterAdoNet(object parameter, string paraName)
        {
            this.parameter = parameter;
            this.parameterName = paraName;
        }

    }
}